package ua.kharkov.khpi.tarasenko.bach.dao.mysql.tree;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import ua.kharkov.khpi.tarasenko.bach.analysis.prefixspan.Item;
import ua.kharkov.khpi.tarasenko.bach.analysis.prefixspan.Node;
import ua.kharkov.khpi.tarasenko.bach.core.db.DBUtils;
import ua.kharkov.khpi.tarasenko.bach.core.db.EShopDataStoreException;
import ua.kharkov.khpi.tarasenko.bach.core.logging.TransactionType;
import ua.kharkov.khpi.tarasenko.bach.dao.core.tree.TreeDAO;

public class TreeDAOImpl implements TreeDAO {

	private static final String TABLE_PATHES = "treepaths";

	private static final String QUERY_INSERT_PATH = "INSERT INTO "
			+ TABLE_PATHES
			+ " (ancestor, descendant, length) SELECT ancestor, ?, length+1 FROM TreePaths WHERE descendant = ? UNION ALL SELECT ?, ?, 0";
	private static final String QUERY_DELETE_ALL_PATHS = "DELETE FROM "
			+ TABLE_PATHES;
	private static final String QUERY_SELECT_ALL_PATHS = "SELECT * FROM "
			+ TABLE_PATHES;
	private static final String QUERY_SELECT_DIRECT_CHILD_NODES_ID = "SELECT descendant FROM "
			+ TABLE_PATHES + " WHERE ancestor=? and length=1";

	private static final String TABLE_NODES = "node";
	private static final String QUERY_INSERT_NODE = "INSERT INTO "
			+ TABLE_NODES + "(item_id, action_id, support)  VALUES(?, ?, ?)";
	private static final String QUERY_DELETE_ALL_NODES = "DELETE FROM "
			+ TABLE_NODES;
	private static final String QUERY_SELECT_ALL_NODES = "SELECT * FROM "
			+ TABLE_NODES;
	private static final String QUERY_RESET_AUTO_INCREMENT = "ALTER TABLE "
			+ TABLE_NODES + " AUTO_INCREMENT = 1";
	private static final String QUERY_GET_NUM_OF_ROWS_IN_TREE_PATH_TABLE = "SELECT count(*) as c FROM " + TABLE_PATHES;

	private Connection conn;

	public TreeDAOImpl(Connection conn) throws EShopDataStoreException {
		if (conn == null) {
			throw new EShopDataStoreException("Connection is null!");
		}
		this.conn = conn;
	}

	@Override
	public int saveNode(Node node) throws EShopDataStoreException {
		PreparedStatement ps = null;

		int nodeId = -1;
		try {
			ps = conn.prepareStatement(QUERY_INSERT_NODE,
					Statement.RETURN_GENERATED_KEYS);
			ps.setInt(1, Integer.valueOf(node.getItem().getItem()));
			ps.setInt(2, node.getItem().getTransactionType().ordinal());
			ps.setInt(3, node.getItem().getSupport());

			ps.executeUpdate();
			ResultSet rs = ps.getGeneratedKeys();
			rs.next();

			nodeId = rs.getInt(1);

			rs.close();
			ps.close();
		} catch (SQLException e) {
			throw new EShopDataStoreException(
					"Error occurred during saving rule", e);
		} finally {
			DBUtils.close(null, ps);
		}
		return nodeId;
	}

	@Override
	public List<List<Integer>> getAllPathes() throws EShopDataStoreException {
		List<List<Integer>> pathes = new ArrayList<List<Integer>>();
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			ps = conn.prepareStatement(QUERY_SELECT_ALL_PATHS);
			rs = ps.executeQuery();

			while (rs.next()) {
				int anc = rs.getInt(1);
				int desc = rs.getInt(2);

				List<Integer> ancDescPair = new ArrayList<Integer>();
				ancDescPair.add(anc);
				ancDescPair.add(desc);
				pathes.add(ancDescPair);
			}

			rs.close();
			ps.close();
		} catch (SQLException e) {
			throw new EShopDataStoreException(
					"Error occurred during getting all paths", e);
		} finally {
			DBUtils.close(null, ps);
		}
		return pathes;
	}

	@Override
	public void savePath(int nodeId, int parentId)
			throws EShopDataStoreException {
		PreparedStatement ps = null;

		try {
			ps = conn.prepareStatement(QUERY_INSERT_PATH);
			ps.setInt(1, nodeId);
			ps.setInt(2, parentId);
			ps.setInt(3, nodeId);
			ps.setInt(4, nodeId);

			ps.executeUpdate();

			ps.close();
		} catch (SQLException e) {
			throw new EShopDataStoreException(
					"Error occurred during saving path", e);
		} finally {
			DBUtils.close(null, ps);
		}
	}

	@Override
	public void deleteTree() throws EShopDataStoreException {
		PreparedStatement ps1 = null;
		PreparedStatement ps2 = null;
		PreparedStatement ps3 = null;

		try {
			ps1 = conn.prepareStatement(QUERY_DELETE_ALL_PATHS);
			ps2 = conn.prepareStatement(QUERY_DELETE_ALL_NODES);
			ps3 = conn.prepareStatement(QUERY_RESET_AUTO_INCREMENT);

			ps1.executeUpdate();
			ps2.executeUpdate();
			ps3.executeUpdate();

		} catch (SQLException e) {
			throw new EShopDataStoreException(
					"Error occurred during deleting tree", e);
		} finally {
			DBUtils.close(null, ps1);
			DBUtils.close(null, ps2);
			DBUtils.close(null, ps3);
		}

	}

	@Override
	public Map<Integer, Node> getAllNodes() throws EShopDataStoreException {
		Map<Integer, Node> nodes = new HashMap<Integer, Node>();
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			ps = conn.prepareStatement(QUERY_SELECT_ALL_NODES);
			rs = ps.executeQuery();

			while (rs.next()) {
				int nodeId = rs.getInt(1);
				int itemId = rs.getInt(2);
				TransactionType userAction = TransactionType.getTransType(rs
						.getInt(3));
				int support = rs.getInt(4);

				Item item = Item.getItem("" + itemId);
				item.setTransactionType(userAction);

				Node node = new Node();
				node.setItem(item);
				node.setSupport(support);

				nodes.put(nodeId, node);
			}

			rs.close();
			ps.close();
		} catch (SQLException e) {
			throw new EShopDataStoreException(
					"Error occurred during getting all nodes", e);
		} finally {
			DBUtils.close(null, ps);
		}
		return nodes;
	}

	@Override
	public List<Integer> getDirectChildNodesId(int parentId)
			throws EShopDataStoreException {
		List<Integer> childIds = new ArrayList<Integer>();
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			ps = conn.prepareStatement(QUERY_SELECT_DIRECT_CHILD_NODES_ID);
			ps.setInt(1, parentId);
			rs = ps.executeQuery();

			while (rs.next()) {
				childIds.add(rs.getInt(1));
			}
		} catch (SQLException e) {
			throw new EShopDataStoreException(
					"Error occurred during getting child nodes' id", e);
		} finally {
			DBUtils.close(null, ps);
		}
		return childIds;
	}

	@Override
	public boolean isTreeInDb() throws EShopDataStoreException {

		boolean result = false;

		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			ps = conn
					.prepareStatement(QUERY_GET_NUM_OF_ROWS_IN_TREE_PATH_TABLE);
			rs = ps.executeQuery();

			while (rs.next()) {

				// One but not zero, cause it could be the dummy root node
				if (rs.getInt("c") > 1) {
					result = true;
				}
			}
		} catch (SQLException e) {
			throw new EShopDataStoreException(
					"Error occurred during checking tree availability in DB", e);
		} finally {
			DBUtils.close(null, ps);
		}

		return result;
	}
}